package com.aier.classes.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import net.eicp.ghostboy.futil.MD5Util;
import net.eicp.ghostboy.futil.RandomUtil;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import com.aier.classes.entity.ClassParent;
import com.aier.classes.entity.ClassStudent;
import com.aier.classes.entity.ClassTeacher;
import com.aier.classes.entity.mapper.ClassParentRowMapper;
import com.aier.classes.entity.mapper.ClassStudentRowMapper;
import com.aier.classes.entity.mapper.ClassTeacherRowMapper;
import com.aier.classes.service.IClassUserService;
import com.aier.common.common.Constant;
import com.aier.common.common.PinyinUtil;
import com.aier.common.common.SystemDateFormatUtil;
import com.aier.common.dao.ICommonDAO;
import com.aier.school.entity.SchoolTeacher;
import com.aier.school.entity.mapper.SchoolTeacherRowMapper;
import com.aier.user.entity.CallRollClass;
import com.aier.user.entity.User;
import com.aier.user.entity.mapper.CallRollClassRowMapper;
import com.aier.user.entity.mapper.UserRowMapper;

@Component("classUserService")
public class ClassUserServiceImpl_J implements IClassUserService{
    @Resource(name = "commonDao")
    private ICommonDAO commonDao;

    public void setCommonDao(ICommonDAO commonDao) {
        this.commonDao = commonDao;
    }

    @Override
    public Map<String, Object> searchClassTeacherList(int page, int pagesize,
            Long cid) {
        String sql = " from class_teacher ct INNER JOIN school_teacher st on ct.tid=st.tid where ct.cid=? ";
        String sql_first = sql;
        return commonDao.queryPage(
            "select ct.*,st.tname as name_teacher,(select name from school_grade where gid=st.gid) as gname " + sql, 
            "select count(*) " + sql_first, page,
            pagesize, new Object[] { cid }, new Object[] { cid },
            new ClassTeacherRowMapper(ClassTeacherRowMapper.AND_TEACHERNAME));   
    }

    @Override
    public ClassTeacher searchClassTeacherByIds(Long uid, Long cid){
        return (ClassTeacher) commonDao.listByObject("select * from class_teacher where tid=(select tid from school_teacher where uid=? " +
                "and sid=(select sid from classes where cid=?)) and cid=?", 
                new Object[] {uid,cid,cid }, new ClassTeacherRowMapper());
    }
    
    @Override
    public int insertClassTeacher(ClassTeacher classTeacher) {
        KeyHolder kh = commonDao.insert(
                ClassTeacherRowMapper.getPreparedStatementCreator(classTeacher),
                new GeneratedKeyHolder());
        if(kh!=null){
            return 1;
        }else{
            return 0;
        }
    }

    @Override
    public int deleteClassTeacher(Long ctid) {
        if (commonDao.update("delete from class_teacher where ctid=?",
                new Object[] {ctid }) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    @Override
    public int updateClassTeacherState(Long ctid,Short state) {
        if (commonDao.update("update class_teacher set isadmin=? where ctid=?",
                new Object[] {state,ctid }) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

//=====================================================================================

    @Override
    public Map<String, Object> searchClassStudentList(int page, int pagesize,
            Long cid) {
        String sql = " from class_student cs where cs.cid=? ";
        String sql_first = sql;
        return commonDao.queryPage(
            "select cs.* " + sql, 
            "select count(*) " + sql_first, page,
            pagesize, new Object[] { cid }, new Object[] { cid },
            new ClassStudentRowMapper());  
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ClassStudent> searchClassStudentList(Long cid){
        return (List<ClassStudent>) commonDao.list("select * from class_student where cid=? ", 
            new Object[] { cid },new ClassStudentRowMapper());
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<ClassStudent> searchClassStudentListBySid(Integer sid){
        return (List<ClassStudent>) commonDao.list("select * from class_student where cid in (select cid from classes where sid=?) ", 
                new Object[] { sid },new ClassStudentRowMapper());        
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<ClassStudent> searchClassStudentByName(String name){
        return (List<ClassStudent>) commonDao.list("select * from class_student where sname=? ", 
                new Object[] { name },new ClassStudentRowMapper());
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<ClassStudent> searchCSByCidName(Long cid,String name){
        return (List<ClassStudent>) commonDao.list("select * from class_student where cid = ? and sname=? ", 
                new Object[] { cid,name },new ClassStudentRowMapper());
    }
    
    @Override
    public long insertClassStudent(Long cid,String name_student,String sex) {
        Timestamp ctime = new Timestamp(new Date().getTime());
        User user_baby = new User(PinyinUtil.HanyuToPinyin(name_student)+
                RandomUtil.genRandomNum(Constant.num_4),MD5Util.getMD5("888888"), name_student, null);
        user_baby.setIsbaby((short) 1);
        if (null != sex && !"".equals(sex)) {
            user_baby.setSex(sex);
        }
        KeyHolder kh = commonDao.insert(
                UserRowMapper.getPreparedStatementCreator(user_baby),
                new GeneratedKeyHolder());
        if(kh!=null){
            ClassStudent cs = new ClassStudent(null,kh.getKey().longValue(), name_student, cid, ctime);
            KeyHolder keyH = commonDao.insert(ClassStudentRowMapper.getPreparedStatementCreator(cs), new GeneratedKeyHolder());
            if (keyH != null) {
                return keyH.getKey().longValue();
            } else {
                return 0;
            }
        }else{
            return 0;
        }
    }

    @Override
    public int deleteClassStudent(Long csid) {
        if (commonDao.update("delete from class_student where csid=?",
                new Object[] {csid }) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    @Override
    public int countClassStudentByCondition(Long uid, Long cid){
        String sql = " from class_student where uid=?";
        if(cid!=null){
            sql += " and cid=" + cid;
        }
        return commonDao.countByQuali(sql, new Object[] {uid });
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<ClassStudent> searchCSBySidName(Integer sid, String name) {
        return (List<ClassStudent>) commonDao.list("select cs.*,c.cname as name_classes from class_student cs left join classes c " +
        		"on cs.cid = c.cid where cs.cid in (select cid from classes where sid = ?) and cs.sname=? ", 
                new Object[] { sid,name },new ClassStudentRowMapper(ClassStudentRowMapper.AND_NAME));
    }
    
    @Override
    public Map<String, Object> searchAddStuIcList(int page, int pagesize,
            Integer sid) {
        String sql = " from school_student_icard ssi left join class_student cs on ssi.csid = cs.csid left join classes c " +
        		"on cs.cid = c.cid left join ic_card icc on ssi.icid = icc.icid where ssi.sid = ? order by ssi.ctime desc";
        String sql_first = sql;
        return commonDao.queryPage(
            "select cs.sname,c.cname as name_classes,concat(icc.ichead,icc.icbody) as cardnum,ssi.ctime" + sql, 
            "select count(*) " + sql_first, page,
            pagesize, new Object[] { sid }, new Object[] { sid },
            new ClassStudentRowMapper(ClassStudentRowMapper.AND_NAME));  
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<SchoolTeacher> searchCTBySidName(Integer sid, String name) {
        return (List<SchoolTeacher>) commonDao.list("select * from school_teacher where sid = ? and tname=? ", 
                new Object[] { sid,name },new SchoolTeacherRowMapper());
    }
    
    @Override
    public Map<String, Object> searchAddTeaIcList(int page, int pagesize,
            Integer sid) {
        String sql = " from school_teacher_icard sti left join school_teacher st on sti.tid = st.tid left join ic_card icc on sti.icid = icc.icid " +
        		"where sti.sid = ? order by sti.ctime desc";
        String sql_first = sql;
        return commonDao.queryPage(
            "select st.tname,concat(icc.ichead,icc.icbody) as cardnum,(select group_concat(cname) from classes where cid in " +
            "(select cid from class_teacher where tid=sti.tid)) as classnams_in,sti.ctime" + sql, 
            "select count(*) " + sql_first, page,
            pagesize, new Object[] { sid }, new Object[] { sid },
            new SchoolTeacherRowMapper(SchoolTeacherRowMapper.AND_CARD));  
    }
    
//=====================================================================================
    
    @Override
    public Map<String, Object> searchClassParentList(int page, int pagesize,
            Long cid) {
        String sql = " from class_parent cp where cp.cid=? order by (select ctime from class_student where csid=cp.csid)";
        String sql_first = sql;
        return commonDao.queryPage(
            "select cp.*,(select sname from class_student where csid=cp.csid) as name_student," +
            "(select phone from user where uid=cp.uid) as tel,(select edate from userapp_user_module where uid = cp.uid and mid = 1) as edate" + sql, 
            "select count(*) " + sql_first, page,
            pagesize, new Object[] { cid }, new Object[] { cid },
            new ClassParentRowMapper(ClassParentRowMapper.AND_STUDENTNAME)); 
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<User> searchAllClassParentList(Long cid) {
        String sql = "select distinct(u.uid),u.* from user u left join class_parent cp on u.uid = cp.uid where cp.cid=? ";
        return (List<User>) commonDao.list(sql, new Object[] { cid },
            new UserRowMapper()); 
    }

    @Override
    public int insertClassParent(ClassParent classParent) {
        KeyHolder kh = commonDao.insert(
                ClassParentRowMapper.getPreparedStatementCreator(classParent),
                new GeneratedKeyHolder());
        if(kh!=null){
            return 1;
        }else{
            return 0;
        }
    }

    @Override
    public int deleteClassParent(Long cpid) {
        try {
            if (commonDao.update("delete from parent_baby where parent_uid=(select uid from class_parent where cpid=?) " +
                    "and baby_uid=(select uid from class_student where csid=(select csid from class_parent where cpid=?))",
                    new Object[] {cpid,cpid }) > 0) {
                commonDao.update("delete from class_parent where cpid=?",
                        new Object[] {cpid });
                return 1;
            } else {
                return 0;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<User> searchParentByBuid(Long buid) {
        String sql = "select u.* from user u left join parent_baby pb on u.uid = pb.parent_uid where pb.baby_uid = ? ";
        return (List<User>) commonDao.list(sql, new Object[] { buid },
            new UserRowMapper()); 
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ClassStudent> searchAllClassStudentList(Long cid) {
    	Timestamp cdate = new Timestamp(new Date().getTime());
        String dateStr = SystemDateFormatUtil.formatDate(cdate, SystemDateFormatUtil.FORMAT_DATE);
        
//        @SuppressWarnings("unchecked")
//        List<CallRollClass> callRollClassList = (List<CallRollClass>) commonDao.list("select * from call_roll_class where cid = ? and cdate = ?",
//                new Object[]{cid,dateStr},new CallRollClassRowMapper());
//
//        // 以点过名,直接返回
//        if (callRollClassList.size() > 0) {
//            return null;
//        }
        String sql = "select cs.* from class_student cs where cs.cid=?";
        return (List<ClassStudent>) commonDao.list(sql,new Object[] { cid },new ClassStudentRowMapper());
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ClassTeacher> searchTeacherByBuid(Long buid) {
        return (List<ClassTeacher>) commonDao.list("select ct.*,st.tname as name_teacher from class_teacher ct left join school_teacher st " +
                "on ct.tid=st.tid left join class_student cs on cs.cid = ct.cid where cs.uid = ?",new Object[]{buid},
                new ClassTeacherRowMapper(ClassTeacherRowMapper.AND_TNAME));
    }

   
    @SuppressWarnings("unchecked")
    @Override
    public List<ClassParent> searchClassParentListByUid(Long uid){
        return (List<ClassParent>) commonDao.list("select cp.*,cs.sname as name_student,cls.cname as name_classes,cls.clogo from class_parent cp " +
                "LEFT JOIN class_student cs on cp.csid=cs.csid " +
                "LEFT JOIN classes cls on cp.cid=cls.cid where cp.uid=?",new Object[]{uid},
                new ClassParentRowMapper(ClassParentRowMapper.AND_NAMES));        
    }


    @SuppressWarnings("unchecked")
    @Override
    public List<ClassTeacher> searchClassTeacherListByUid(Long uid){
        return (List<ClassTeacher>) commonDao.list("select ct.*,cls.cname as name_classes,cls.clogo,st.tname as name_teacher from class_teacher ct " +
                "LEFT JOIN classes cls on ct.cid=cls.cid " +
                "LEFT JOIN school_teacher st on ct.tid=st.tid where st.uid=?",new Object[]{uid},
                new ClassTeacherRowMapper(ClassTeacherRowMapper.AND_NAMES));          
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<ClassParent> searchCPByCondition(Long uid, Long cid, Long csid) {
        return (List<ClassParent>) commonDao.list("select * from class_parent where uid = ? and cid = ? and csid = ?",new Object[]{uid,cid,csid},
                new ClassParentRowMapper());   
    }

    @Override
    public int deleteParentClass(Long uid, Long cid){
        try {
            if (commonDao.update("delete from parent_baby where parent_uid=? " +
                    "and baby_uid in (select uid from class_student where csid in " +
                    "(select csid from class_parent where uid=? and cid=?))",
                    new Object[] {uid,uid,cid }) > 0) {
                commonDao.update("delete from class_parent where uid=? and cid=?",
                        new Object[] {uid,cid });
                return 1;
            } else {
                return 0;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }
    
    @Override
    public int deleteTeacherClass(Long uid, Long cid){
        try {
            if (commonDao.update("delete from class_teacher where cid=? " +
                    "and tid in (select tid from school_teacher where uid=?)",
                    new Object[] {cid,uid }) > 0) {
                return 1;
            } else {
                return 0;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }        
    }
    
    @Override
    public ClassStudent searchClassStudentByBuid(Long buid){
        return (ClassStudent) commonDao.listByObject("select * from class_student where uid=?", 
                new Object[] {buid }, new ClassStudentRowMapper());
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ClassParent> searchClassParentListSid_account(Integer sid) {
        return (List<ClassParent>) commonDao.list("select cp.*,cs.sname as name_student,u.account as account from class_parent cp " +
                "LEFT JOIN class_student cs on cp.csid=cs.csid LEFT JOIN user u on cp.uid=u.uid where cp.cid in " +
                "(select cid from classes where sid=?)",new Object[]{sid},
                new ClassParentRowMapper(ClassParentRowMapper.AND_ACCOUNT)); 
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ClassParent> searchClassParentListCidList_account(List<Long> cidList) {
        String temp_cids = "(";
        for(int i=0;i<cidList.size();i++){
            if(i==cidList.size()-1){
                temp_cids = temp_cids + cidList.get(i) + ")";
            }else{
                temp_cids = temp_cids + cidList.get(i) + ",";
            }
        }
        String sql = "select cp.*,cs.sname as name_student,u.account as account from class_parent cp " +
                    "LEFT JOIN class_student cs on cp.csid=cs.csid LEFT JOIN user u on cp.uid=u.uid where cp.cid in " + temp_cids;
        return (List<ClassParent>) commonDao.list(sql, new Object[] {}, new ClassParentRowMapper(ClassParentRowMapper.AND_ACCOUNT));         

    }
    
    @Override
    public User searchBabyByUid(Long parent_uid, Long baby_uid) {
        return (User) commonDao.listByObject("select u.* from user u left join parent_baby pb on u.uid = pb.baby_uid where pb.parent_uid = ? " +
                "and pb.baby_uid = ?",new Object[]{parent_uid,baby_uid},new UserRowMapper());
    }

    @Override
    public int searchSchoolBuyMethodByCid(Long cid) {
        String method = (String)commonDao.listByObject("select s.method from school s left join classes c on s.sid = c.sid where c.cid = ?", 
                new Object[]{cid},
                new RowMapper<String>() {
                @Override
                public String mapRow(ResultSet rs, int arg1)
                    throws SQLException {
                    return rs.getString("method");
                }
            }
        );
        if (null == method || "".equals(method)) {
            return 1;
        } else {
            return Integer.valueOf(method);
        }
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ClassStudent> searchCSByCondition(Long mid) {
        List<ClassStudent> csList = (List<ClassStudent>) commonDao.list("select cs.* from class_student cs LEFT JOIN school_message_read smr " +
        		"on cs.csid = smr.csid where smr.mid = ?",new Object[]{mid},new ClassStudentRowMapper());
        for (ClassStudent cs:csList) {
        	cs.setCpList((List<ClassParent>)commonDao.list("select * from class_parent cp LEFT JOIN school_message_read smr on cp.cpid = smr.cpid " +
            		"where smr.mid = ? and smr.csid = ?",new Object[]{mid,cs.getCsid()},new ClassParentRowMapper()));
        	List<ClassParent> cpList=cs.getCpList();
        	if(cpList!=null || cpList.size()>0 ){
        	 	for(ClassParent cpreant:cpList){
            		User user =(User)commonDao.listByObject("select * from user where uid=?", 
            				new Object[] { cpreant.getUid() },
                            new UserRowMapper());
            		cpreant.setHeadimg(user.getHeadimg());
            	}
        	}
        }
        return csList;
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<ClassParent> searchCPByCondition(Long cid, Long csid) {
        return (List<ClassParent>) commonDao.list("select cp.*,u.account from class_parent cp left join `user` u on cp.uid = u.uid " +
        		"where csid = ? and cid = ?",new Object[]{csid,cid},
                new ClassParentRowMapper(ClassParentRowMapper.AND_ACC));   
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ClassStudent> searchCSAbsentByMonth(Long cid,String month) {
        return (List<ClassStudent>) commonDao.list("select cs.*," +
        		"(select count(*) from call_roll where csid = cs.csid and state = 1 and left(cdate,7) = ?) as leav," +
        		"(select count(*) from call_roll where csid = cs.csid and state = 2 and left(cdate,7) = ?) as absent from class_student cs " +
        		"where cs.cid = ? and (select count(*) from call_roll where csid = cs.csid and left(cdate,7) = ?) > 0 " +
        		"order by (leav+absent) desc,absent desc",new Object[]{month,month,cid,month},
        		new ClassStudentRowMapper(ClassStudentRowMapper.AND_CR));
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ClassTeacher> searchClassAdminTeacher(Long cid) {
        return (List<ClassTeacher>) commonDao.list("select ct.*,st.tname as name_teacher,u.account from class_teacher ct left join " +
        		"school_teacher st on ct.tid = st.tid left join `user` u on st.uid = u.uid where ct.cid = ? and ct.isadmin = 1", 
                new Object[] {cid }, new ClassTeacherRowMapper(ClassTeacherRowMapper.AND_ACCOUNT));
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<ClassStudent> searchClassParentApp(Long cid) {
        List<ClassStudent> csList = (List<ClassStudent>) commonDao.list(
                "select cs.*,u.sex from class_student cs left join user u on cs.uid = u.uid where cs.cid = ?",
                new Object[]{cid},new ClassStudentRowMapper(ClassStudentRowMapper.AND_SEX));
        for (ClassStudent cs:csList) {
            cs.setCpList((List<ClassParent>)commonDao.list("select cp.*,u.account from class_parent cp left join user u on cp.uid = u.uid " +
                    "where cp.csid = ?",new Object[]{cs.getCsid()},new ClassParentRowMapper(ClassParentRowMapper.AND_ACC)));
        }
        return csList;
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public Map<String, Object> searchClassStudent(int page,int pagesize,Long cid) {
        
        String sql = " from class_student cs left join user u on cs.uid = u.uid where cs.cid = ?";
        String sql_first = sql;
        Map<String,Object> map = commonDao.queryPage("select cs.*,u.sex" + sql, 
            "select count(*) " + sql_first, page,
            pagesize, new Object[] { cid }, new Object[] { cid },
            new ClassStudentRowMapper(ClassStudentRowMapper.AND_SEX));   
        List<ClassStudent> csList = (List<ClassStudent>) map.get(ICommonDAO.OBJ_LIST);
        
        for (ClassStudent cs:csList) {
            cs.setCpList((List<ClassParent>)commonDao.list("select cp.*,u.account from class_parent cp left join user u on cp.uid = u.uid " +
                    "where cp.csid = ?",new Object[]{cs.getCsid()},new ClassParentRowMapper(ClassParentRowMapper.AND_ACC)));
        }
        map.put(ICommonDAO.OBJ_LIST, csList);
        
        return map;
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<ClassTeacher> searchClassTeacherListApp(Long cid) {
        return (List<ClassTeacher>) commonDao.list("select ct.*,st.tname as name_teacher,u.account from class_teacher ct left join " +
                "school_teacher st on ct.tid = st.tid left join `user` u on st.uid = u.uid where ct.cid = ?", 
                new Object[] {cid }, new ClassTeacherRowMapper(ClassTeacherRowMapper.AND_ACCOUNT));
    }
    
    @Override
    public int searchClassParentCount(Long cid) {
        
        String str = (String) commonDao
                .listByObject("select count(*) as count from class_parent where cid=?",
                        new Object[] {cid},
                        new RowMapper<String>() {
                            @Override
                            public String mapRow(ResultSet rs, int arg1)
                                throws SQLException {
                                return rs.getString("count");
                            }
                        });
        return Integer.valueOf(str);
    }

    @Override
    public int updateClassStudentName(Long csid, String name) {
        return commonDao.update("update class_student set sname = ? where csid = ?",new Object[]{name,csid});
    }

    @Override
    public int updateClassStudentSex(Long uid, String sex) {
        return commonDao.update("update user set sex = ? where uid = ?",new Object[]{sex,uid});
    }
    
}
